package com.conversationboard.site;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import com.conversationboard.database.BatchQueries;
import com.conversationboard.database.Database;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.User;

/**
 * A startup job to count up all of the posts and record them per user on the database. Once this one-off job has been
 * run, then each post a user makes will increment the counter.
 * 
 * 
 * @author Keith Watson
 *
 */

public class CountPosts {

	public static void countAllPosts() throws SQLException {

		/* 1. Create a hash map of login IDs and integers */

		Map<String, Integer> users = new HashMap<String, Integer>();

		/* 2. Go through the user table and populate the hash map, setting the count to zero */

		Connection connection = null;

		try {
			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.GET_ALL_LOGINIDS);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {
				String loginId = resultSet.getString("loginid");
				users.put(loginId, 0);
			}

			statement.close();

		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				Logger.log(new Date(), "Failed to close connection in CountPosts job (getting login IDs)", User.getSystemUser());
			}
		}

		/* 3. Iterate through all the messages, 50,000 at a time and update each count in the hash table */

		try {
			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.GET_ALL_LOGINIDS_FROM_MESSAGES);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {
				String loginId = resultSet.getString("userid");
				
				if (users.containsKey(loginId)) {
					users.put(loginId, (users.get(loginId) + 1));
				}
				
			}

			statement.close();

		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				Logger.log(new Date(), "Failed to close connection in CountPosts job (counting messages)", User.getSystemUser());
			}
		}

		/* 4. Loop through the hash map and make updates to the users table with all the results. Add them all to a batch
		      and then execute the batch in one shot. */

		try {
			connection = Database.getConnection();
			PreparedStatement statement = connection.prepareStatement(BatchQueries.UPDATE_USER_MESSAGE_COUNT);

			for (String loginId : users.keySet()) {
				int messageCount = users.get(loginId);
				statement.setInt(1, messageCount);
				statement.setString(2, loginId);
				statement.addBatch();
			}

			statement.executeBatch();
			statement.close();

		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				Logger.log(new Date(), "Failed to close connection in CountPosts job (counting messages)", User.getSystemUser());
			}
		}
		
		Logger.log(new Date(), "Finished updating users' message post counts", User.getSystemUser());

	}

}
